package com.witframework.core.persistence;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.witframework.core.BeanFactory;

public class JdbcTools {
	private static IBaseJdbcDao getJDBCDao() {
		return (IBaseJdbcDao) BeanFactory.getBean("baseJdbcDao");
	}
	public static SimpleJdbcTemplate getSimpleJdbcTemplate(){
		return getJDBCDao().getSimpleJdbcTemplate();
	}
	public static void insert(final String tableName,final Map<String, Object> data) {
		getJDBCDao().insert(tableName, data);
	}
	public static void insertByWebMap(final String tableName,final Map<String, String> data) {
		getJDBCDao().insertByWebMap(tableName, data);
	}
	public static void update(String tableName,Map<String,Object> data){
		getJDBCDao().update(tableName, data);
	}
	public static void updateByWebMap(String tableName,Map<String,String> data){
		getJDBCDao().updateByWebMap(tableName, data);
	}
	public static void deleteBy(String tableName, String key, String vlaue) {
		getJDBCDao().deleteBy(tableName, key, vlaue);
	}
	public static void deleteByMap(String tableName,Map<String,Object> data){
		getJDBCDao().delete(tableName, data);
	}
	public static void deleteByWebMap(String tableName,Map<String,String> data){
		getJDBCDao().deleteByWebMap(tableName, data);
	}
	public static List<?> queryForList(final String sql, final Object... args) {
		return getJDBCDao().queryForList(sql, args);
		// return (List) new TransactionTemplate().doInTransaction(
		// new ServiceCallback(){
		// public Object execute(){
		// return getJDBCDao().queryForList(sql, args);
		// }
		// });

	}
	public static Map queryForMap(final String sql, final Object... args) {
		return getJDBCDao().queryForMap(sql, args);
	}
	public static List pageQuery(PageSortModel psm, String sql, Object... args) {
		return getJDBCDao().pageQuery(psm, sql, args);
	}

	public static List pageQuery(int start, int end, String sql, Object... args) {
		PageSortModel psm = new PageSortModel();
		psm.setPageSize(end - start);
		psm.setPageNo(start / (end - start));
		return getJDBCDao().pageQuery(psm, sql, args);
	}
	public static DatabaseMetaData getDatabaseMetaData() throws SQLException{
		Connection conn = null;
		try {
			conn = getJDBCDao().getDataSource().getConnection();
			DatabaseMetaData meta=conn.getMetaData();
			return meta;
		}catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	public static List<Map> getTables(){
		Connection conn = null;
		try {
			conn = getJDBCDao().getDataSource().getConnection();
			DatabaseMetaData meta=conn.getMetaData();
			ResultSet rs= meta.getTables(null, null, "%", new String[]{"TABLE"});
			return rs2MapList(rs);
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	public static List<Map> getAllColumnMetaData(){
		Connection conn = null;
		try {
			conn = getJDBCDao().getDataSource().getConnection();
			DatabaseMetaData meta=conn.getMetaData();
			ResultSet rs= meta.getColumns(null, null, "%","%");
			return rs2MapList(rs);
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	public static List<Map> getColumnMetaData(String tableName){
		Connection conn = null;
		try {
			conn = getJDBCDao().getDataSource().getConnection();
			DatabaseMetaData meta=conn.getMetaData();
			ResultSet rs= meta.getColumns(null, null, tableName,"%");
			return rs2MapList(rs);
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	/**
	 * 
	 * @param tableName
	 * @param type PKS:getPrimaryKeys;IKS:getImportedKeys;EKS:getExportedKeys
	 * @return
	 */
	public static List<Map> getTableMetaData(String tableName,String type){
		return getJDBCDao().getTableMetaData(tableName, type);	
	}
	public static ResultSetMetaData getResultSetMetaData(String tableName)
			throws SQLException {
		Connection conn = null;
		try {
			conn = getJDBCDao().getDataSource().getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
			ResultSetMetaData rsmd = rs.getMetaData();			
			return rsmd;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	/**
	 * 将ResultSet转化成map
	 */
	public static Map<String, Object> rs2Map(ResultSet rs) throws SQLException {
		if (null == rs) {
			return null;
		}
		Map<String, Object> map = new HashMap<String, Object>();
		ResultSetMetaData meta = rs.getMetaData();
		int count = meta.getColumnCount();
		for (int i = 0; i < count; i++) {
			map.put(meta.getColumnName(i + 1), rs.getObject(i + 1));
		}
		return map;
	}
	
	public static List<Map> rs2MapList(ResultSet rs) throws SQLException {
		return BaseJdbcDao.rs2MapList(rs);		
	}
	public static void main(String[] s) throws SQLException {
		getJDBCDao();
		long start = System.currentTimeMillis();
		for (int i = 0; i < 1000; i++) {
			System.out.println(i);
			ResultSetMetaData rsmd = JdbcTools.getResultSetMetaData("ad_user");
			int numberOfColumns = rsmd.getColumnCount();
			System.out.println("numberOfColumns=" + numberOfColumns);
			System.out.println("columnName\tcolumnType\tcolumnTypeName\tprecision\tscale\tcolumnDisplaySize\tisAutoIncrement\tisNullable");
			for (int column = 1; column <= numberOfColumns; column++) {
				// String tableName=rsmd.getTableName(column);//获取指定列的名称。
				String columnName = rsmd.getColumnName(column);
				int columnType = rsmd.getColumnType(column);// 获取指定列的数据库特定的类型名称。
				String columnTypeName = rsmd.getColumnTypeName(column);// 获取指定列的数据库特定的类型名称。
				int precision = rsmd.getPrecision(column);// 获取指定列的指定列宽。
				int scale = rsmd.getScale(column);// 获取指定列的小数点右边的位数。
				int columnDisplaySize = rsmd.getColumnDisplaySize(column);
				boolean isAutoIncrement = rsmd.isAutoIncrement(column); // 指示是否自动为指定列进行编号。
				boolean isNullable = ResultSetMetaData.columnNullable == rsmd
						.isNullable(column); // 指示指定列中的值是否可以为 null。
				System.out.println(columnName + "\t"+columnType+"\t" + columnTypeName + "\t"
						+ precision + "\t" + scale + "\t" + columnDisplaySize
						+ "\t" + isAutoIncrement + "\t" + isNullable);
			}
			List<Map> orgList = (List<Map>) JdbcTools.queryForList("select * from ad_org");
			for (Map org : orgList) {
				Iterator itor = org.keySet().iterator();
				while (itor.hasNext()) {
					String key = (String) itor.next();
					Object value = org.get(key);
					System.out.print(key + "=" + value + "\t");
				}
				System.out.println();
			}

		}

		System.out.println("完成:" + (System.currentTimeMillis() - start) + "ms");
	}
}
